USE [master]
GO
/****** Object:  StoredProcedure [dbo].[VDBAReplicationLatencyALLCLEAR]    Script Date: 01/20/2015 13:55:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[VDBAReplicationLatencyALLCLEAR]
/*
This SP is for the logic of when to send the all clear

*/
as
Begin
Declare @exit char(1)
Declare @AlertTime datetime
Declare @SendEmail char(1)

set @alertTime = getdate()
set @exit = 'N'
set @SendEmail = 'N'


While @exit = 'N'
Begin

        
      --select @AlertTime = MAX(alertTime) from master.dbo.ReplicationLatencyTracking
      
      if @AlertTime < DATEADD(mi,-5,getdate())
      Begin
       -- Send alert
       
       Print 'Send All Clear'
       set @exit = 'Y'
       set @SendEmail = 'Y'
      end
      
      if DATEDIFF(mm,@AlertTime,getdate()) >= 60
      Begin
      Print 'Max time running reached'
      set @exit = 'Y'
      End
      
      -- Add the delay before checking again. For now it is set for a 1 minute 
          WAITFOR DELAY '00:01';
  End
      
 if @SendEmail = 'Y'
 Begin
 declare @sub varchar(256)
  set @sub = 'All Clear - Replication Latency on  ' + @@SERVERNAME

exec msdb.dbo.sp_send_dbmail
@recipients= 'alerts@800loanmart.com, ittxtalerts@800loanmart.com,appdev@800loanmart.com',
@profile_name ='DW-PROD01 Mail',
@subject = @sub,
@body = 'All Clear'
end

 
end    
   
